package com.itheima.demo;

import com.itheima.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/*
    实现Mybatis对数据库的CRUD(增删改查)操作


 */
public class CrudDemo1 {
    private SqlSession session = null;

    @Before
    public void before() throws IOException {
        //1.读取核心配置文件
        InputStream is = Resources.getResourceAsStream("sqlMapper.xml");

        //2.构建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //3.获取SqlSession对象
        session = factory.openSession();
    }

    @After
    public void after() {
        //5.事务提交
        session.commit();
        //6.释放资源
        session.close();
    }


    //2.根据id查询      查询一条selectOne
    @Test
    public void testFindById() {
        //调用statement对象
        User user = session.selectOne("one.findById", 1);
        System.out.println(user);

    }

    //3.新增操作 (更新必须要事务提交)
    @Test
    public void testSave() {
        User u = new User();
        u.setName("贾诩");
        u.setSex("男");
        u.setCountry("魏");
        session.insert("one.SaveUser", u);
    }

    //4.根据id更新操作
    @Test
    public void testUpdate(){
        User user = session.selectOne("one.findById", 7);
        user.setName("司马懿");
        session.update("one.update",user);

    }

    //5.根据id删除操作
    @Test
    public void testDelete(){
        session.delete("one.deleteById",7);

    }
    //6.统计
    @Test
    public void testTotal(){
        int i = session.selectOne("one.total");
        System.out.println(i);
    }

    //模糊查询
    @Test
    public void testFindLike(){
//        List<User> list = session.selectList("one.findLikeName", "%陆%");
        List<User> list = session.selectList("one.findLikeName", "戏");
        for (User user : list) {
            System.out.println(user);
        }
    }

    //根据用户名和密码查询
    @Test
    public void testFindNameAndPwd(){
        Map<String,String> map = new HashMap<String,String>();
        map.put("name","郭嘉");
        map.put("country","魏");
        User user = session.selectOne("one.findNameAndCountry", map);
        System.out.println(user);
    }
    //统计出男女分别的数量
    @Test
    public void testFindSexCount(){
        //集合嵌套
        List<Map<String,Long>> map = session.selectList("one.findSexCount");
        /*
        map1[sex=女,count(*) = 2]
        map2[sex=男,count(*) = 3]

        map[女=2,男=3]
         */
        System.out.println(map);
//
        for (Map<String,Long> stringMap : map) {
            Object sex =stringMap.get("sex");

            Long total = stringMap.get("count(sex)");

            System.out.println(sex+":"+total);
        }
    }
}
